﻿namespace AnyOffice.DAL
{
    using AnyOffice.Model;
    using Microsoft.ApplicationBlocks.Data;
    using System;
    using System.Data;
    using System.Data.SqlClient;
    using System.Text;

    public class MobileDao
    {
        public bool Delete(SqlConnection conn, int mobileId, string userId)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("SET NOCOUNT ON ");
            builder.Append("DELETE FROM [dbo].[Mobile] ");
            builder.Append("WHERE ");
            builder.Append("\t[MobileId] = @MobileId ");
            builder.Append("\tAND [FromId] = @UserId ");
            SqlParameter[] commandParameters = new SqlParameter[2];
            commandParameters[0] = new SqlParameter("@MobileId", SqlDbType.Int);
            commandParameters[0].Value = mobileId;
            commandParameters[1] = new SqlParameter("@UserId", SqlDbType.NVarChar, 20);
            commandParameters[1].Value = userId;
            SqlHelper.ExecuteNonQuery(conn, CommandType.Text, builder.ToString(), commandParameters);
            return true;
        }

        public int Insert(SqlConnection conn, MobileData data)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("SET NOCOUNT ON ");
            builder.Append("INSERT INTO [dbo].[Mobile] ");
            builder.Append("( ");
            builder.Append("\t[FromId], ");
            builder.Append("\t[Phone], ");
            builder.Append("\t[Content], ");
            builder.Append("\t[SendTime], ");
            builder.Append("\t[SendFlag] ");
            builder.Append(") ");
            builder.Append("VALUES ");
            builder.Append("( ");
            builder.Append("\t@FromId, ");
            builder.Append("\t@Phone, ");
            builder.Append("\t@Content, ");
            builder.Append("\t@SendTime, ");
            builder.Append("\t@SendFlag ");
            builder.Append(") ");
            builder.Append("SELECT SCOPE_IDENTITY() AS RetValue ");
            SqlParameter[] commandParameters = new SqlParameter[5];
            commandParameters[0] = new SqlParameter("@FromId", SqlDbType.NVarChar, 20);
            commandParameters[0].Value = data.FromId;
            commandParameters[1] = new SqlParameter("@Phone", SqlDbType.NVarChar, 20);
            commandParameters[1].Value = data.Phone;
            commandParameters[2] = new SqlParameter("@Content", SqlDbType.NText);
            commandParameters[2].Value = data.Content;
            commandParameters[3] = new SqlParameter("@SendTime", SqlDbType.DateTime);
            commandParameters[3].Value = data.SendTime;
            commandParameters[4] = new SqlParameter("@SendFlag", SqlDbType.Int);
            commandParameters[4].Value = data.SendFlag;
            object obj2 = SqlHelper.ExecuteScalar(conn, CommandType.Text, builder.ToString(), commandParameters);
            if (obj2 != null)
            {
                return Convert.ToInt32(obj2);
            }
            return -1;
        }

        public DataTable SelectPaged(SqlConnection conn, int pagesize, int pageindex, bool docount, string userId)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("SET NOCOUNT ON ");
            builder.Append("IF(@docount=1) ");
            builder.Append("\tSELECT COUNT([MobileId]) FROM [dbo].[Mobile] WHERE [FromId] = @UserId ");
            builder.Append("ELSE ");
            builder.Append("\tBEGIN ");
            builder.Append("\t\tDECLARE @IndexTable TABLE(ID int identity(1,1),PK int) ");
            builder.Append("\t\tDECLARE @PageLowerBound int ");
            builder.Append("\t\tDECLARE @PageUpperBound int ");
            builder.Append("\t\tSET @PageLowerBound=(@pageindex-1)*@pagesize ");
            builder.Append("\t\tSET @PageUpperBound=@PageLowerBound+@pagesize ");
            builder.Append("\t\tSET rowcount @PageUpperBound ");
            builder.Append("\t\t ");
            builder.Append("\t\tINSERT INTO @IndexTable(PK) SELECT [MobileId] FROM [dbo].[Mobile] WHERE [FromId] = @UserId ORDER BY [SendTime] DESC ");
            builder.Append("\t\t ");
            builder.Append("\t\tSELECT * ");
            builder.Append("\t\tFROM ");
            builder.Append("\t\t\t[dbo].[Mobile], ");
            builder.Append("\t\t\t@indextable t ");
            builder.Append("\t\tWHERE ");
            builder.Append("\t\t\t[MobileId] = t.PK ");
            builder.Append("\t\t\tAND t.ID>@PageLowerBound ");
            builder.Append("\t\t\tAND t.ID<=@PageUpperBound ");
            builder.Append("\t\tORDER BY ");
            builder.Append("\t\t\t[SendTime] DESC ");
            builder.Append("\tEND ");
            SqlParameter[] commandParameters = new SqlParameter[4];
            commandParameters[0] = new SqlParameter("@pagesize", SqlDbType.Int);
            commandParameters[0].Value = pagesize;
            commandParameters[1] = new SqlParameter("@pageindex", SqlDbType.Int);
            commandParameters[1].Value = pageindex;
            commandParameters[2] = new SqlParameter("@docount", SqlDbType.Bit);
            commandParameters[2].Value = docount;
            commandParameters[3] = new SqlParameter("@UserId", SqlDbType.NVarChar, 20);
            commandParameters[3].Value = userId;
            DataSet set = SqlHelper.ExecuteDataset(conn, CommandType.Text, builder.ToString(), commandParameters);
            set.Tables[0].TableName = "Mobile";
            return set.Tables[0];
        }

        public DataTable SendToIds(SqlConnection conn, string toIds, MobileData data)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("SET NOCOUNT ON ");
            builder.Append("INSERT INTO [dbo].[Mobile] ");
            builder.Append("( ");
            builder.Append("\t[FromId], ");
            builder.Append("\t[ToId], ");
            builder.Append("\t[Phone], ");
            builder.Append("\t[Content], ");
            builder.Append("\t[SendTime], ");
            builder.Append("\t[SendFlag] ");
            builder.Append(") ");
            builder.Append("SELECT ");
            builder.Append("\t@FromId, ");
            builder.Append("\t[UserId], ");
            builder.Append("\t[Mobile],\t ");
            builder.Append("\t@Content, ");
            builder.Append("\t@SendTime, ");
            builder.Append("\t@SendFlag ");
            builder.Append("FROM ");
            builder.Append("\t[dbo].[Users] ");
            builder.Append("WHERE ");
            builder.Append("\tCHARINDEX(',' + [UserId] + ',', ',' + CAST(@ToIds AS nvarchar(4000)) + ',') > 0 ");
            builder.Append("\tAND ISNULL([Mobile],'') <> '' ");
            builder.Append("SELECT ");
            builder.Append("\t[UserId], ");
            builder.Append("\t[UserName] ");
            builder.Append("FROM ");
            builder.Append("\t[dbo].[Users] ");
            builder.Append("WHERE ");
            builder.Append("\tCHARINDEX(',' + [UserId] + ',', ',' + CAST(@ToIds AS nvarchar(4000)) + ',') > 0 ");
            builder.Append("\tAND ISNULL([Mobile],'') = '' ");
            SqlParameter[] commandParameters = new SqlParameter[6];
            commandParameters[0] = new SqlParameter("@ToIds", SqlDbType.NText);
            commandParameters[0].Value = toIds;
            commandParameters[1] = new SqlParameter("@FromId", SqlDbType.NVarChar, 20);
            commandParameters[1].Value = data.FromId;
            commandParameters[2] = new SqlParameter("@Phone", SqlDbType.NVarChar, 20);
            commandParameters[2].Value = data.Phone;
            commandParameters[3] = new SqlParameter("@Content", SqlDbType.NText);
            commandParameters[3].Value = data.Content;
            commandParameters[4] = new SqlParameter("@SendTime", SqlDbType.DateTime);
            commandParameters[4].Value = data.SendTime;
            commandParameters[5] = new SqlParameter("@SendFlag", SqlDbType.Int);
            commandParameters[5].Value = data.SendFlag;
            DataSet set = SqlHelper.ExecuteDataset(conn, CommandType.Text, builder.ToString(), commandParameters);
            set.Tables[0].TableName = "Users";
            return set.Tables[0];
        }
    }
}

